package com.keepsoft.integrating.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.time.Year;
import java.util.*;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.keepsoft.integrating.dao.IntegratingDao;

import static com.keepsoft.integrating.main.Main.DAY;
import static com.keepsoft.integrating.main.Main.MONTH;
import static com.keepsoft.integrating.main.Main.YEAR;

public class IntegratingDaoImpl implements IntegratingDao {

    public static final Logger logger = LoggerFactory.getLogger(IntegratingDaoImpl.class);
    private static boolean flag = true;

    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public List<String> getEquNames(Integer type) {
        List<String> list = new ArrayList<String>();
        String sql;
        if (type == 1) {
            sql = "SELECT equ_name FROM MONITOR_EQU_DATA_YLCHILD GROUP BY equ_name";
        } else {
            sql = "SELECT equ_name FROM MONITOR_EQU_DATA_SCHILD GROUP BY equ_name";
        }
        list = jdbcTemplate.queryForList(sql, String.class);
        return list;
    }

    public String get2DaysAgo(Integer type, String name) {
        String sql;
        String dayTime = null;
        if (type == 1) {
            sql = "SELECT CONVERT(VARCHAR(10),DATEADD(DAY, -1,MAX(Insert_time)),120) dayTime FROM MONITOR_EQU_DATA_YLCHILD WHERE equ_name = ? ";
        } else {
            sql = "SELECT CONVERT(VARCHAR(10),DATEADD(DAY, -1,MAX(Insert_time)),120) dayTime FROM MONITOR_EQU_DATA_SCHILD WHERE equ_name = ? ";
        }
        try {
            dayTime = jdbcTemplate.queryForObject(sql, new Object[]{name}, new RowMapper<String>() {
                public String mapRow(ResultSet rs, int rowNum) throws SQLException {

                    return rs.getString("dayTime");
                }
            });
        } catch (EmptyResultDataAccessException e) {
        }
        return dayTime;
    }

    public String getMinTime(Integer type, String name) {
        String sql;
        String dayTime = null;
        if (type == 1) {
            sql = "SELECT CONVERT(VARCHAR(10),MIN(Insert_time),120) dayTime FROM MONITOR_EQU_DATA_YLCHILD WHERE equ_name = ? ";
        } else {
            sql = "SELECT CONVERT(VARCHAR(10),MIN(Insert_time),120) dayTime FROM MONITOR_EQU_DATA_SCHILD WHERE equ_name = ? ";
        }
        try {

            dayTime = jdbcTemplate.queryForObject(sql, new Object[]{name}, new RowMapper<String>() {
                public String mapRow(ResultSet rs, int rowNum) throws SQLException {

                    return rs.getString("dayTime");
                }
            });
        } catch (EmptyResultDataAccessException e) {
        }
        return dayTime;
    }


    public Integer saveBackupData(Integer type, String name, String time) {
        String sql;
        if (type == 1) {
            sql = "INSERT INTO MONITOR_EQU_DATA_YLCHILD_BAK (equ_name,pipe_havewater,pipe_full,pipe_waterdepth,round_width,round_foul,round_real_width,"
                    + "orthogon_pipe_width,orthogon_pipe_height,kanat_foul,orthogon_pipe_realheight,round_flow_rate,orthogon_kanat_flow_rate,pipe_flow,"
                    + "if_rain,people,equ_id,Insert_time,temperature,accum_flow)"
                    + "SELECT equ_name,pipe_havewater,pipe_full,pipe_waterdepth,round_width,round_foul,round_real_width,"
                    + "orthogon_pipe_width,orthogon_pipe_height,kanat_foul,orthogon_pipe_realheight,round_flow_rate,orthogon_kanat_flow_rate,pipe_flow,"
                    + "if_rain,people,equ_id,Insert_time,temperature,accum_flow "
                    + "FROM MONITOR_EQU_DATA_YLCHILD WHERE equ_name = ? AND Insert_time < ?";
        } else {
            sql = "INSERT INTO MONITOR_EQU_DATA_SCHILD_BAK (equ_id,equ_name,river_ifwater,river_flow,river_width,river_height,flow_speed,flow,pH,DO,"
                    + "temperature,conductivity,TN,TP,if_smell,wave,water_quality_type,water_blackening,day_ifrain,recorder,Insert_time) "
                    + "SELECT equ_id,equ_name,river_ifwater,river_flow,river_width,river_height,flow_speed,flow,pH,DO,"
                    + "temperature,conductivity,TN,TP,if_smell,wave,water_quality_type,water_blackening,day_ifrain,recorder,Insert_time "
                    + "FROM MONITOR_EQU_DATA_SCHILD WHERE equ_name = ? AND Insert_time < ?";
        }
        return jdbcTemplate.update(sql, new Object[]{name, time});
    }

    public Integer saveAvgData(Integer type, String name, String time) {
        String sql;
        if (type == 1) {
            sql = "INSERT INTO MONITOR_EQU_DATA_YLCHILD_RESULT "
                    + "(equ_name, Insert_time, pipe_full, round_foul, round_real_width, round_flow_rate, pipe_flow, equ_id, rain_number) "
                    + "SELECT a.*,b.rain_number FROM "
                    + "(SELECT equ_name ,CONVERT(VARCHAR(10),Insert_time,120) Insert_time,AVG(pipe_full) pipe_full,AVG(CAST(round_foul AS NUMERIC(38,10))) round_foul, "
                    + "AVG(round_real_width) round_real_width,AVG(round_flow_rate) round_flow_rate,AVG(pipe_flow) pipe_flow,equ_id "
                    + "FROM MONITOR_EQU_DATA_YLCHILD WHERE equ_name = ? AND Insert_time < ? GROUP BY equ_name,equ_id,CONVERT(VARCHAR(10),Insert_time,120)) a "
                    + "LEFT JOIN MONITOR_DATA_RAIN b ON a.Insert_time = b.rain_day ORDER BY a.equ_name";
        } else {
            sql = "INSERT INTO MONITOR_EQU_DATA_SCHILD_RESULT "
                    + "(equ_name,Insert_time,river_ifwater,river_flow,river_width,river_height,flow_speed,flow,pH,DO,temperature,conductivity,"
                    + "TN,TP,if_smell,water_quality_type,water_blackening,day_ifrain,recorder,rain_number) "
                    + "SELECT a.*,b.rain_number FROM "
                    + "(SELECT equ_name,CONVERT(VARCHAR(10),Insert_time,120) Insert_time,river_ifwater,river_flow,AVG(river_width) river_width,"
                    + "AVG(river_height) river_height,AVG(flow_speed) flow_speed,AVG(flow) flow,AVG(pH) pH,AVG(DO) DO,AVG(temperature) temperature,"
                    + "AVG(conductivity) conductivity,AVG(TN) TN,AVG(TP) TP,if_smell,water_quality_type,water_blackening,day_ifrain,recorder "
                    + "FROM MONITOR_EQU_DATA_SCHILD WHERE equ_name = ? AND Insert_time < ? "
                    + "GROUP BY equ_name, CONVERT(VARCHAR(10),Insert_time,120), river_ifwater,river_flow,if_smell,water_quality_type,water_blackening,day_ifrain,recorder )a "
                    + "LEFT JOIN MONITOR_DATA_RAIN b ON a.Insert_time = b.rain_day ORDER BY a.equ_name";
        }
        return jdbcTemplate.update(sql, new Object[]{name, time});
    }

    public Integer remove2DaysAgoData(Integer type, String name, String time) {
        String sql;
        if (type == 1) {
            sql = "DELETE FROM MONITOR_EQU_DATA_YLCHILD WHERE equ_name = ? AND Insert_time < ?";
        } else {
            sql = "DELETE FROM MONITOR_EQU_DATA_SCHILD WHERE equ_name = ? AND Insert_time < ?";
        }
        return jdbcTemplate.update(sql, new Object[]{name, time});
    }

    /**
     * 模拟批量插入数据
     *
     * @param type
     */
    @Override
    public void insertData(Integer type) {
        if (!flag) return;
        String sql;
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        List<Date> list = getTimeSegment(YEAR, MONTH, DAY);
        List<String> equNames = getEquNames(type);
        if (type == 1) {
            sql = "INSERT INTO MONITOR_EQU_DATA_YLCHILD (equ_name,pipe_havewater,pipe_full,pipe_waterdepth,round_width,round_foul,round_real_width,"
                    + "orthogon_pipe_width,orthogon_pipe_height,kanat_foul,orthogon_pipe_realheight,round_flow_rate,orthogon_kanat_flow_rate,pipe_flow,"
                    + "if_rain,people,equ_id,Insert_time,temperature,accum_flow)"
                    + "VALUES (?,null,null,0.02,1.2,0.05,0.95,0,0,0,0,0,0,0,0,null,null,?,null,null)";
        } else {
            sql = "INSERT INTO MONITOR_EQU_DATA_SCHILD (equ_id,equ_name,river_ifwater,river_flow,river_width,river_height,flow_speed,flow,pH,DO,"
                    + "temperature,conductivity,TN,TP,if_smell,wave,water_quality_type,water_blackening,day_ifrain,recorder,Insert_time) "
                    + "VALUES (null,?,1,1,25,0.14,0.05,0.2,7,4,null,599,0,0,'否','','IV类','轻度黑臭',0,null,?)";
        }
        long startTime = System.currentTimeMillis();
        for (String name : equNames) {
            for (Date date : list) {
                jdbcTemplate.update(sql, new Object[]{name, fmt.format(date)});
            }
        }
        long endTime = System.currentTimeMillis();
        logger.info("inserting into table is running:" + (endTime - startTime) + "ms");
        flag = false;
    }

//    public static void main(String[] args) {
//        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//        List<Date> list = getTimeSegment(2018, 1, 24);
//        for(Date date : list){
//            System.out.println(fmt.format(date));
//        }
//        System.out.println(list.size());
//    }

    public static List<Date> getTimeSegment(int year, int month, int day) {
        Calendar cal = Calendar.getInstance();
        cal.set(year, month - 1, day, 0, 0, 0);
        cal.set(Calendar.MILLISECOND, 0);
        long startTime = cal.getTimeInMillis();
        cal.set(year, month - 1, day, 23, 59, 59);
        long endTime = cal.getTimeInMillis();
        final int seg = 5 * 60 * 1000;//五分钟
        ArrayList<Date> result = new ArrayList<Date>((int) ((endTime - startTime) / seg + 1));
        for (long time = startTime; time <= endTime; time += seg) {
            result.add(new Date(time));
        }
        return result;
    }

}
